# -*- coding=utf-8 -*-
import urllib2
import re
import gzip
import StringIO
import csv
import xlrd
import xlwt

#读取出让公告         编码和容积率
fname = u"顺德土地信息（出让公告）.xls"
print "打开顺德土地信息（出让公告），读取数据"
bk = xlrd.open_workbook(fname)

shxrange = range(bk.nsheets)
sheetname = u"出让公告"

try:
    sh = bk.sheet_by_name(sheetname)
except:
    print "no sheet in %s named Sheet1" % fname
print "获取数据"

#获取行数
nrows = sh.nrows
#获取列数
ncols = sh.ncols
print "nrows %d, ncols %d" % (nrows,ncols)

cr_dic={}
vindx= 1
while vindx<nrows:
    print sh.cell(vindx,1).value+":"+sh.cell(vindx,5).value
    cr_dic[sh.cell(vindx,1).value] = sh.cell(vindx,5).value
    vindx+=1

#读取成交公示
fname = u"顺德土地信息（成交公示）.xls"
print "打开顺德土地信息（成交公示），读取数据"
bk = xlrd.open_workbook(fname)

shxrange = range(bk.nsheets)
sheetname = u"成交公示"

try:
    sh = bk.sheet_by_name(sheetname)
except:
    print "no sheet in %s named Sheet1" % fname
print "获取数据"

#获取行数
nrows = sh.nrows
#获取列数
ncols = sh.ncols
print "nrows %d, ncols %d" % (nrows,ncols)
vindx= 1
resulrlist=[]
while vindx<nrows:
    col = 0
    itemlist=[]
    rongjilv=''
    while col<ncols:
        vvalue = sh.cell(vindx,col).value
        #找容积率
        if col==1:
            if cr_dic.has_key(vvalue):
                rongjilv = cr_dic[vvalue]
        itemlist.append(vvalue)
        #
        print u'行:'+str(vindx)+u"列:"+str(col)+u"   ="+ vvalue
        col+=1
    itemlist.append(rongjilv)
    vindx+=1
    resulrlist.append(itemlist)


#写入新表

f= xlwt.Workbook()#创建工作簿
sheetname = "成交公示"
sheet = f.add_sheet(unicode(sheetname,'utf-8'),cell_overwrite_ok=True)
#创建表头
sheet.write(0,0,u'公示时间')
sheet.write(0,1,u'地块编码')
sheet.write(0,2,u"地块位置")
sheet.write(0,3,u"土地用途")
sheet.write(0,4,u"土地面积(公顷)")
sheet.write(0,5,u"出让年限")
sheet.write(0,6,u"成交价")
sheet.write(0,7,u"受让单位")
sheet.write(0,8,u"备注")
sheet.write(0,9,u"容积率")

nrows = len(resulrlist)
i=0
while i<nrows:
    wdic = resulrlist[i]
    k=i+1
    print "新的数据，开始写入档案"
    sheet.write(k,0,wdic[0])
    sheet.write(k,1,wdic[1])
    sheet.write(k,2,wdic[2])
    sheet.write(k,3,wdic[3])
    sheet.write(k,4,wdic[4])
    sheet.write(k,5,wdic[5])
    sheet.write(k,6,wdic[6])
    sheet.write(k,7,wdic[7])
    sheet.write(k,8,wdic[8])
    sheet.write(k,9,wdic[9])
    i+=1

f.save( u'顺德土地信息(出让公告)新.xls')